# -*- coding: UTF-8 -*-

import gol
import MySQLdb
import time
from config.getConfig import getConfig

# 数据库IP地址
SQL_IP = getConfig("db.ini", "database", "dbhost")
# 数据库用户名
SQL_USER = getConfig("db.ini", "database", "dbuser")
# 数据库密码
SQL_PSW = getConfig("db.ini", "database", "dbpassword")
# 数据库名称
SQL_DB = getConfig("db.ini", "database", "dbname")

def UpdateSql():
    """
    用于从预测队列中取出结果，然后将结果更新到数据库中
    :return: 无
    """
    while True:
        while gol.get_queue_size('predict_line') > 0:
            ####
            start = time.clock()
            ####
            conn = MySQLdb.connect(SQL_IP, SQL_USER, SQL_PSW, SQL_DB, charset='utf8')
            db = conn.cursor()
            data = gol.get_queue_value('predict_line')
            # print(data)
            ###############################################################################
            #                            更新classes表                                     #
            ###############################################################################
            # SELECT寻找数据库中对应教学楼、教室的信息
            sql_1 = "SELECT * FROM classes " \
                    "WHERE classes.Building='{}' AND classes.Classes='{}'"\
                .format(data['BuildName'],
                        data['ClassName'])
            db.execute(sql_1)

            if len(db.fetchall()) > 0:
                # 该教学楼在数据库中以存在，用UPDATE更新数据
                sql_2 = "UPDATE classes " \
                        "SET num={},Percentage={} " \
                        "WHERE Building='{}' AND Classes='{}'"\
                    .format(data['Num'],
                            100 * float(data['Num']) / float(data['MaxNum']),
                            data['BuildName'],
                            data['ClassName'])
                db.execute(sql_2)
            else:
                # 该教室为第一次，利用INSERT插入数据库
                sql_2 = '''
                INSERT INTO classes 
                (Building, Classes, MaxNum, num, Percentage)
                VALUES
                ('{}', '{}', {}, {}, {})
                '''.format(data['BuildName'],
                           data['ClassName'],
                           data['MaxNum'],
                           data['Num'],
                           100 * float(data['Num']) / float(data['MaxNum']))
                db.execute(sql_2)

            ###############################################################################
            #                               更新time表                                     #
            ###############################################################################
            # 将当前预测结果插入到time表中
            sql_3 = '''
            INSERT INTO time
            (Building, Class, num, time)
            VALUES
            ('{}', '{}', {}, '{}')
            '''.format(data['BuildName'],
                       data['ClassName'],
                       data['Num'],
                       data['Time'])
            db.execute(sql_3)

            # 检测每个教室所拥有的记录数量，最多100条记录，超过的记录删除
            sql_4 = '''
            DELETE FROM time
            WHERE time NOT IN (
                SELECT time
                FROM (
                    SELECT time
                    FROM time
		            WHERE Building='{}' AND Class='{}'
                    ORDER BY time DESC
                    LIMIT 100 -- 最多保存100条记录
                ) foo
            ) AND Building='{}' AND Class='{}';
            '''.format(data['BuildName'],
                       data['ClassName'],
                       data['BuildName'],
                       data['ClassName'])
            db.execute(sql_4)

            ###############################################################################
            #                             更新building表                                   #
            ###############################################################################
            # 获取一个教学楼中所有教室人数之和
            sql_5 = "SELECT SUM(MaxNum) as MaxNum,SUM(num) as num FROM classes " \
                    "WHERE Building='{}'"\
                .format(data['BuildName'])
            db.execute(sql_5)
            buildRes = db.fetchall()

            # 更新每栋教学楼的总人数
            sql_6 = '''
            UPDATE building 
            SET MaxNum={}, num={},Percentage={}
            WHERE Building='{}'
            '''.format(buildRes[0][0],
                       buildRes[0][1],
                       float(buildRes[0][1]) / float(buildRes[0][0]),
                       data['BuildName'])
            db.execute(sql_6)

            ###############################################################################
            #                            更新BoxInf表                                      #
            ###############################################################################
            # SELECT寻找数据库中对应教学楼、教室的信息
            sql_7 = "SELECT * FROM BoxInf " \
                    "WHERE BoxInf.Building='{}' AND BoxInf.Classes='{}'" \
                .format(data['BuildName'],
                        data['ClassName'])
            db.execute(sql_7)

            if len(db.fetchall()) > 0:
                # 该教学楼在数据库中以存在，用UPDATE更新数据
                sql_8 = "UPDATE BoxInf " \
                        "SET bbox='{}' " \
                        "WHERE Building='{}' AND Classes='{}'" \
                    .format(data['Bbox'],
                            data['BuildName'],
                            data['ClassName'])
                db.execute(sql_8)
            else:
                # 该教室为第一次，利用INSERT插入数据库
                sql_9 = '''
                            INSERT INTO BoxInf 
                            (Building, Classes, bbox)
                            VALUES
                            ('{}', '{}', '{}')
                            '''.format(data['BuildName'],
                                       data['ClassName'],
                                       data['Bbox'])
                print(type(data['Bbox']))
                print(sql_9)
                db.execute(sql_9)

            conn.commit()
            print('预测结果已更新到数据库!')
            print('待更新数据库数据数量：{}'.format(gol.get_queue_size('predict_line')))
            # gol.put_queue_value('inf_line', '预测结果已更新到数据库!')
            db.close()
            ###
            lapse = time.clock() - start
            gol.set_value('sql_time', gol.get_value('predict_time') + lapse)
            gol.set_value('sql_times', gol.get_value('predict_times') + 1)
            print("数据库更新平均时间{}".format(gol.get_value('predict_time') / gol.get_value('predict_times')))
            ###
        time.sleep(0.01)




